from django.shortcuts import render,HttpResponse,redirect
from django.template.context_processors import csrf
from django.views.decorators.csrf import csrf_exempt
import json
import pymysql
import time
import random
import re
from app.function import translate

# Create your views here.
def index(request):
    # 获取用户提交方式
    print("request的方法:{}".format(request.method))

    # 在URL上获取参数
    print("参数：{}".format(request.GET))

    # 在请求体中传输数据
    print("POST:{}".format(request.POST))

    return HttpResponse('1')


def redirect_baidu(request):
    # 浏览器的重定向
    return redirect("https://www.baidu.com")

def login(request):
    if request.method == 'GET':
        return render(request,'login.html')
    else:
        print(request.POST)
        return HttpResponse("登录成功")

def pic(request):
    '''发送图片'''
    path = r"D:\Software\python\project\Django_demo\django_demo\app\templates\1.png"
    file_one = open(path, "rb")
    return HttpResponse(file_one.read(), content_type='image/jpg/png')

# 延迟发送
def delay(request):
    print(request.GET)
    sec = random.random()*3
    time.sleep(sec)
    return HttpResponse('延迟了%s'%sec)


# 模拟搜索PMC数据库返回json文件
def search(request):
    with open('./data/后端返回.json','r',encoding='utf-8') as f:
        data = f.read()
    return HttpResponse(data,content_type='json')

@csrf_exempt
def database(request):
    '''数据库操作'''

    # 得到请求
    data = eval(json.loads(request.body.decode('utf-8'))['data'])  # type:list
    print(data,type(data))

    # 打开数据库连接
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='123456',
                         port=3306,
                         db='Spider'
                         )

    cursor = db.cursor()

    table = 'TextInfoTable'

    # 查询所有数据
    cursor.execute('''select * from {table} limit 2'''.format(table=table))
    result = cursor.fetchall()

    # 将结果转为json格式放入列表
    keyList = []
    dataList = []

    for i in range(len(cursor.description)):
        key = cursor.description[i][0]
        keyList.append(key)

    for row in result:
        rowJson = {}

        for i in range(len(row)):
            rowJson[keyList[i]] = row[i]
        dataList.append(rowJson)

    # 封装为JSON传给前端
    data = {}
    data['code'] = 200
    data['data'] = dataList

    db.close()

    return HttpResponse(json.dumps(data),content_type='json')


# 前端发送下载请求
@csrf_exempt
def download(request):
    '''发送下载请求'''

    '''得到数据'''
    # 得到请求
    dataList = eval(json.loads(request.body.decode('utf-8'))['data'])  # type:list

    for i in dataList:
        PMCID = i.get('PMCID')
        print(PMCID)

    return HttpResponse('收到！')

'''数据库查询'''
# 查询整行数据
def database_select_all(request):
    param = request.GET.get('PMCIDs')
    if not param: return HttpResponse('{"data":["Wrong"]}')
    term = request.GET.get('term')
    if not term: return HttpResponse('{"data":["未传入参数！"]}')

    table = 'TextTable'

    dataList = []  # 存储含有关键词的句子的列表

    # 打开数据库连接
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='123456',
                         port=3306,
                         db='Spider'
                         )

    cursor = db.cursor()

    for PMCID in param.split(','):
        sql = '''select content,secTitle from {table} where PMCID="{PMCID}"'''.format(
            table=table,
            PMCID=PMCID)
        sql = '''select content,PMCID,secID from {table}
                '''.format(
            table=table,)

        cursor.execute(sql)

        result = cursor.fetchall()

        for row in result:
            content = row[0]  # 文章内容
            for text in content.split('.'):
                pattern = re.compile(r' {term} '.format(term=term), re.S)
                r = re.search(pattern, text)
                if r:
                    # 过滤--清除标签[\d],如[1]、[2-3]
                    text = re.sub(r"\[\d+\]|\[\d+–\d+\]","",text)
                    tempJson = {'content':text,'PMCID':row[1],'secID':row[2]}
                    dataList.append(tempJson)

            # 避免循环过多程序卡死
            if len(dataList) >= 1000: break

    db.close()

    return HttpResponse(json.dumps({"data":dataList}))

# 查询状态码
def database_select_state(request):
    param = request.GET.get('PMCIDs')
    table = 'TextInfoTable'

    # 打开数据库连接
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='123456',
                         port=3306,
                         db='Spider'
                         )

    cursor = db.cursor()

    dataList = []
    for PMCID in param.split(','):

        sql = '''select state from {table} where PMCID="{PMCID}"'''.format(
            table=table,
            PMCID=PMCID)

        cursor.execute(sql)

        result = cursor.fetchone()
        # 判断状态是否存在
        if(result):
            state = result[0]
        else:
            state = 0

        dataList.append({'PMCID':PMCID,'state':state})

    db.close()

    return HttpResponse(json.dumps({"dataList":dataList}))

'''数据库更新'''
def database_update(request):
    param = request.GET.get('PMCIDs') # type:str
    table = 'TextInfoTable'
    state = int(request.GET.get('state'))

    # 打开数据库连接
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='123456',
                         port=3306,
                         db='Spider'
                         )

    cursor = db.cursor()
    for PMCID in param.split(','):
        print(PMCID)

        cursor.execute('''update {table} set state={state}
                          where PMCID="{PMCID}"'''.format(
            table=table,
            state=state,
            PMCID=PMCID))
    db.commit()

    db.close()

    return HttpResponse('ok')


# 查询文章内容
def database_select_article(request):
    param = request.GET.get('PMCID')
    table = 'TextTable'

    # 打开数据库连接
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='123456',
                         port=3306,
                         db='Spider'
                         )

    cursor = db.cursor()

    '''得到文章所有的内容和子标题'''
    dataList = []
    for PMCID in param.split(','):

        sql = '''select secTitle,content from {table} where PMCID="{PMCID}"'''.format(
            table=table,
            PMCID=PMCID)

        cursor.execute(sql)

        result = cursor.fetchall()

        for row in result:
            secTitle = row[0]
            content = row[1]

            dataList.append({'secTitle': secTitle, 'content': content})

    db.close()

    return HttpResponse(json.dumps({"textList": dataList}))


# 翻译标题
def database_translate_title(request):

    PMCID = 'PMC2185519'
    table = 'textInfoTable'

    # 打开数据库连接
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='123456',
                         port=3306,
                         db='Spider'
                         )

    cursor = db.cursor()

    sql = '''select title from {table} where PMCID="{PMCID}"'''.format(
        table=table,
        PMCID=PMCID)

    cursor.execute(sql)

    result = cursor.fetchall()[0]

    # 调用翻译接口
    result = translate(result[0])
    d_result = json.loads(result)
    print(d_result,type(d_result))

    # 正确调用API则向数据库中添加翻译结果
    if d_result['trans_result']:
        print('翻译成功')
        trans_result_list = d_result['trans_result'] # 翻译结果
        trans_result = trans_result_list[0]
        dst = trans_result['dst']

        # 将翻译后的存入数据库
        sql = '''UPDATE {table} SET zh_title="{dst}"
                 WHERE PMCID="{PMCID}";
                 '''.format(
            table=table,
            dst=dst,
            PMCID=PMCID,
        )
        print(sql)
        cursor.execute(sql)
        db.commit()

    db.close()

    return HttpResponse(dst)